<?php
include_once('dao_common.php');
include_once('dao_log.php');
include_once('dao_tables.php');
//error_reporting(E_ALL);
/*
helper函数,封装简单通用的查询/更改操作
*/
/*
1：数据的type, 如为字符型，无特别判断 ，只需加上引号，或like的逻辑，如为数值类型，则需判断
2: 数据的判断，一般是=,或<,>，like等。
3: 数据是否一定要存在
*/
/*
type类型，judge比较规则(=,<,>,like【小写】),must是否必须，logic组合逻辑and/or/,
blank是否处理空字符，空字符表示已经赋值但长度为0的字符串
如果blank=1，则虽然为空，依旧生成类似field=''的条件或值，而不是跳过

*/
/*默认的meta*/
global $metanum;
global $metastr;






//字符串中如包含一些和sql语法冲突的字符，会有可能导致sql错误
//替换'
function check_sql_str(&$str)
{
	$str = mysql_escape_string($str);
	$str = str_ireplace(' where'," \where",$str);//增加这一行，对所有值都过滤where
}

function item_cond(&$cond,$table,$field,$value,&$meta,&$msg=NULL)
{
	$ret = 0;
	$msg='';
	$cond='';
	$debug=1;
	if(!isset($value))
	{
		return $ret;
	}
	//判断数据类型，

	if($meta['type']==TYPE_NUM)//数字
	{
		$value = trim($value);
		if(strlen($value)==0)return 0;
		if(!is_numeric($value))
		{
			//错误
			$msg='value must be numeric:'.$field.',but is:'.$value;
			$ret=-1;
			if($debug)
			{
				echo $msg."\r\n";
			}
			return $ret;
		}
	}
	else
	{
		check_sql_str($value);
		if(strlen($value)==0&& $meta['blank']==0)//空字符串不比较
		{
			return $ret;
		}
		if($meta['judge']=='like') //like比较
		{
			$value='%'.$value.'%';
		}
		//加上引号
		check_sql_str($value);
		$value ="'".$value."'";
	}
	$judge=$meta['judge'];
	if(!isset($judge))
	{
		$judge='=';
	}
	$cond = "$table.$field  $judge  $value"; //拼成' table.f_name='somthing' '的格式
	return 0;
}

//遍历预定义的meta，将值加到sql条件从句。
//构造出来的条件从句不带 where关键字
function build_where(&$where,$table,&$values,&$metas,&$msg, $debug=0)
{
	$ret = 0;
	$where = '';
	$index=0;
	$msg='';
	$num_of_where= sizeof($values);
	$num_of_where_used = 0;

	DAO_Log::debug(__FUNCTION__.' metas = '.count($metas));
	if(sizeof($metas)==NULL)
	{
		DAO_Log::debug(__FUNCTION__.': input metas =NULL');
		return 0;
	}
	foreach($metas as $field=> $meta)
	{
		$cond='';
		$value = $values[$field];
		if(isset($value) )
		{
			$cond='';
			//组合单个值
			$ret = item_cond($cond,$table,$field,$value,$meta,$msg);
			if($ret!=0)
			{
				DAO_Log::debug(__FUNCTION__.':item_cond return error.'.$msg);
				break;
			}
		}
		else
		{
			//字段不存在，判断其字段是否必要
			// DAO_Log::debug(__FUNCTION__.':'.$field.' is not set.the must setting='.$meta['must'].'.\r\n');
			if($meta['must']==1)
			{
				$msg='value must exist but miss:'.$field;
				$ret = -1;
				break;
			}
		}
		if(strlen($cond))
		{
			$logic=$meta['logic'];
			$where .="\r\n        ".$logic.' '.$cond;
			$num_of_where_used++;
		}
	}//foreach field

	DAO_Log::debug(__FUNCTION__.':result is:['.trim($where).']');
	return $ret;
}

//helper functions-----------------------------
function add_sql_item(&$fields,&$values,$field,$value)
{
	if(strlen($fields)==0)
	{
		$fields=$field;
	}
	else
	{
		$fields =$fields.','.$field;
	}

	if(strlen($values)==0)
	{
		$values = $value;
	}
	else
	{
		$values =$values.','.$value;
	}


}

function add_sql_item_str(&$fields,&$values,$field,$value)
{
	check_sql_str($value);
	$value="'".$value."'";
	add_sql_item($fields,$values,$field,$value);
}

/*遍历预定义的meta，将值加到sql条件从句，类似。
(f_id,f_name) values (100,'name')
适用于insert
如无值，则返回空串 ''
*/
function build_insert(&$mod,&$values_array,&$metas,&$msg,$debug=0)
{
	$vstr;
	$fstr;
	$ret=0;
	$mod='';
	DAO_Log::debug(__FUNCTION__.' metas = '.count($metas));
	if(sizeof($metas)==0)
	{
		$msg=__FUNCTION__.': input metas =NULL';
		DAO_Log::debug($msg);
		return -1;
	}
	if(sizeof($values_array)==0)
	{
		$msg=__FUNCTION__.': input values =NULL';
		DAO_Log::debug($msg);
		return -1;
	}
	$fieldstr='';
	$valuesstr='';
	$i=0;
	foreach ($values_array as $key=>$values)
	{
		$vstr='';
		$fstr='';
		//var_dump($values);
		foreach ($metas as $field=>$meta)
		{
			$value = $values[$field];
			if(isset($value) )
			{
				if($meta['type']==TYPE_NUM)
				{
					$value=trim($value);
					if(!is_numeric($value))
					{
						$msg='value must be numeric:'.$field.',but is:['.$value.']';
						$ret=-1;
						return $ret;
					}
					//DAO_Log::debug( __FUNCTION__.':buildModify:int:'.$field.'='.$value);
					add_sql_item($fstr,$vstr,$field,$value);
				}
				else
				//if($meta['type']==TYPE_STR)
				{
					//DAO_Log::debug( __FUNCTION__.':str:'.$field.'='.$value);
					//插入空值
					if(strlen($value)==0&&$meta['blank']==1)
					{
						$value='';
					}
					add_sql_item_str($fstr,$vstr,$field,$value);
				}
			}
			else
			{
				//判断必须的值是否存在
				//DAO_Log::debug( __FUNCTION__.':condition not exist for '.$field.'.meta:'.var_export($meta,true));
				if($meta['must']==1)
				{
					$ret = -1;
					$msg='value must exist but miss:'.$field;
					break;
				}
			}

		}//foreach $metas
		if($ret==0)
		{
			if(strlen($fstr)&&strlen($fieldstr)==0)
			{
				$fieldstr="($fstr)";
			}
			if(strlen($vstr))
			{
				$i++;
				if($i>1)
				{
					$valuesstr.=",\r\n";
				}
				$valuesstr.="\t($vstr)";
			}
		}
	}//for each values
	//DAO_Log::debug( __FUNCTION__.':'.$fstr.'=>'.$vstr.'\r\n');
	//-----------------------------------
	if($ret==0&&strlen($valuesstr)&&strlen($fieldstr))
	{
		$mod = "$fieldstr values \r\n$valuesstr";
	}
	if(strlen($mod)==0)
	{
		DAO_Log::debug( __FUNCTION__.':Empty value for insert');
		$ret = -1;
	}
	DAO_Log::debug(__FUNCTION__.":result is:\r\n".trim($mod).'<');
	return $ret;

}



/*遍历预定义的meta，将值加到sql条件从句，类似。
(f_id,f_name) values (100,'name')
适用于update,insert,不适用于delete
*/
function build_update(&$mod,&$values,&$metas,&$msg,$debug=0)
{
	$vstr;

	$ret=0;
	$mod='';
	$num_of_update = sizeof($values);
	$num_of_update_used = 0;
	DAO_Log::debug(__FUNCTION__.' metas = '.count($metas));
	if(sizeof($metas)==0)
	{
		$msg=__FUNCTION__.': input metas =NULL';
		DAO_Log::debug($msg);
		return -1;
	}
	if(sizeof($values)==0)
	{
		$msg=__FUNCTION__.': input values =NULL';
		DAO_Log::debug($msg);
		return -1;
	}
	foreach ($metas as $field=>$meta)
	{
		$value = $values[$field];
		if(isset($value) )
		{
			//DAO_Log::debug(__FUNCTION__.'>$field:type='.$meta['type']);
			if($meta['type']==TYPE_NUM)
			{
				$value =trim($value);
				if(!is_numeric($value))
				{
					$msg='value must be numeric:'.$field.',but is:'.$value;
					$ret=-1;
					if($debug)
					{
						echo $msg."\r\n";
					}
					return $ret;
				}
				$vstr=$field.'='.$value;
				$num_of_update_used++;
			}
			else
			{
				//空值
				if(strlen($value)==0)
				{
					if($meta['blank']==1)
					{
						$value='';
					}
					else{
						continue;
					}
				}
				check_sql_str($value);
				$vstr=$field."='".$value."'";
				$num_of_update_used++;
			}
			if(strlen($mod)>0)$mod.=',';
			$mod.=$vstr;
		}
		else
		{
			if($meta['must']==1)
			{
				$ret = -1;
				$msg='value must exist but miss:'.$field;
				break;
			}
		}
		//----------
	}
	DAO_Log::debug( __FUNCTION__.':update string:'.$mod);
	return $ret;

}

//将where从句 和 扩展条件（如 limit ,orderby by group by等，增加到基本的sql后面

function  attach_sql(&$sql,$where,$exsql)
{
	DAO_Log::debug(__METHOD__.' where :['.trim($where).'] exSQL['.trim($exsql).']');
	if(strlen($where)>0)
	{
		//如原sql语句包含where，则不需要增加where关键字，否则增加
		if(strpos($sql,' where')===false&&strpos($where,'where ')===false)
		{
			$sql.=' where 1=1';
		}
		$sql .='  '. $where;
	}

	//增加where后的 sql从句,如 orderby limit等
	if(strlen($exsql)>0)
	{
		if(strpos($sql,'where')===false)
		{
			$sql.=' where 1=1';
		}
		$sql=rtrim($sql);
		$sql.="\r\n        ".$exsql;
	}
}


/*
时间范围条件拼装
sometime between $begin and $end
sometime > $begin
sometime < $end
*/
function  time_condition($name,$begin,$end)
{
	$begin = trim($begin);
	$end  = trim($end);
	$timestr = '';
	//echo (__FUNCTION__.': $name:begin=[$begin];end=[$end],timeStr=$timestr');
	if( strlen($begin)>0&&strlen($end)>0)
	{
		$timestr = "$name between  '$begin' and '$end'";
	}
	if(strlen($begin)==0&&strlen($end)>0)
	{
		$timestr = $name."< '$end'";
	}
	if(strlen($begin)>0&&strlen($end)==0)
	{
		$timestr = $name."> '$begin'";
	}
	DAO_Log::debug(__FUNCTION__.": $name:begin=[$begin];end=[$end],timeStr=$timestr");
	if(strlen($timestr))
	{
		$timestr =' and '.$timestr.' ';
	}
	return $timestr;
}

function put_field_to_data(&$data,$fields)
{
	$newdata=array();
	foreach($data as $key=>$value)
	{
		$i=0;
		foreach($fields  as $fieldkey=>$field_value)
		{
			$field_name = '';
			if(is_string($field_value))
			{
				$field_name = $field_value;
			}else{
				$field_name = $field_value->name;
			}
			$newdata[$key][$field_name]=$value[$i];
			$i++;
		}
	}

	$data = $newdata;
}

if($argc>1)
{
	if($argv[1]=='ut_build_insert')
	{
		$metas['id']=$metanum;
		$metas['name']=$metastr;
		$data['id']=1;
		$data['name']='joah';
		$dataArray[]=$data;
		$data['id']=2;
		$data['name']='marray';
		$dataArray[]=$data;
		$data['id']=3;
		$data['name']='tom';
		$dataArray[]=$data;
		qcslog::setMax();
		$ret = build_insert($mod,$dataArray,$metas,$msg);
		echo "ret=$ret,msg=$msg\r\n";
		echo "$mod\r\n";
	}

	if($argv[1]=='ut_checksqlstr')
	{
		$str="%test 'abc'";
		check_sql_str($str);
		echo "check result: $str \r\n";
	}
}
?>